﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Web.Configuration;

namespace WebApplication1
{
    public partial class cdreq : System.Web.UI.Page
    {
        string issno,cno;
        int no,copy,c;
        public string acno;

        //no-account no from session
        //copy-no copies of cd in database
        //c-to check whether cd is already issued
        //cno-cd number

        SqlConnection cnn = new SqlConnection(WebConfigurationManager.AppSettings["connect"]);
        protected void Page_Load(object sender, EventArgs e)
        {
            acno = Session[no].ToString();
            TextBox1.Text = acno;
            if (IsPostBack == false)
            {
                
                catdrop.Items.Add("Select category");
                cdnamedrop.Items.Add("Select CD Name");
                cnn.Open();

                //check no of cds taken
                SqlCommand cmd = new SqlCommand("select no_cd from account where accno='" + acno + "'", cnn);
                SqlDataReader dr3 = cmd.ExecuteReader();
                if (dr3.HasRows == true)
                {
                    while (dr3.Read())
                        no = Convert.ToInt32(dr3["no_cd"]);
                    dr3.Close();
                    if (no == 3)
                    {
                        Response.Write("<script>alert('You cannot take more than 3 cd's')</script>");
                        Response.Redirect("~/Users/usracc.aspx");
                    }
                }
                catdrop.Focus();

                //category for dropdownlist
                SqlCommand cmd2 = new SqlCommand("select distinct cat from cdinfo", cnn);
                SqlDataReader dr2 = cmd2.ExecuteReader();
                while (dr2.Read())
                    catdrop.Items.Add(Convert.ToString(dr2["cat"]));
                dr2.Close();
                cnn.Close();
                Label5.Visible = false;
                Label6.Visible = false;
                Label7.Visible = false;
            }
        }

        protected void savebtn_Click(object sender, EventArgs e)
        {
            cnn.Open();

            //selecting issno from cdissue
            SqlCommand cmd1 = new SqlCommand("select top 1 issno from cdissue order by issno desc", cnn);
            SqlDataReader dr = cmd1.ExecuteReader();
            if (dr.HasRows == false)
            {
                issno = acno + "1";
            }
            else
            {
                while (dr.Read())
                    issno = Convert.ToString(Convert.ToInt32(dr.GetValue(0)) + 1);
            }
            dr.Close();

            //selecting the cd details from cdinfo
            SqlCommand cmd2 = new SqlCommand("select cdno from cdinfo where name='"+cdnamedrop.SelectedItem+"'", cnn);
            SqlDataReader dr0 = cmd2.ExecuteReader();
                while (dr0.Read())
                    cno = Convert.ToString(dr0.GetValue(0));
                dr0.Close();

            //inserting into cdissue table
            SqlCommand cmd = new SqlCommand("insert into cdissue(issno,cdno,custno,status,active) values('" + issno + "','" + cno + "','" +acno + "','" + txtstatus.Text + "','true')", cnn);
            SqlDataReader dr3 = cmd.ExecuteReader();
            dr3.Close();
            txtstatus.Text = " ";
            cnn.Close();
        }

        protected void catdrop_SelectedIndexChanged(object sender, EventArgs e)
        {
            cnn.Open();
            SqlCommand cmd = new SqlCommand("select name from cdinfo where cat='"+catdrop.SelectedItem+"'", cnn);
            SqlDataReader dr3 = cmd.ExecuteReader();
            while (dr3.Read())
                cdnamedrop.Items.Add(dr3["name"].ToString());
            dr3.Close();
            cnn.Close();
        }

        protected void cdnamedrop_SelectedIndexChanged(object sender, EventArgs e)
        {
            cnn.Open();

            //check cd already issued
            SqlCommand cmd1 = new SqlCommand("select count(d.cdno) from cdissue d,account a,cdinfo c where d.active='true' and c.cdno=d.cdno and d.custno=a.accno and a.accno='" + acno + "' and c.name='" + cdnamedrop.SelectedItem+"'", cnn);
            SqlDataReader dr = cmd1.ExecuteReader();
                while (dr.Read())
                    c = Convert.ToInt32(dr.GetValue(0));
                dr.Close();
                if (c.Equals(0))
                {
                    SqlCommand cmd = new SqlCommand("select copies from cdinfo where name='" + cdnamedrop.SelectedItem + "'", cnn);
                    SqlDataReader dr3 = cmd.ExecuteReader();
                    while (dr3.Read())
                        copy = Convert.ToInt32(dr3.GetValue(0));
                    dr3.Close();
                    if (copy == 0)
                    {
                        Label5.Text = "CD IS NOT AVAILABLE BUT YOU CAN ONLY RESERVE IT";
                        Label5.Visible = true;
                        txtstatus.Text = "Reserve";
                    }
                    else
                        txtstatus.Text = "Request";
                }
                else
                {
                    Response.Write("<script>alert('You have already taken this cd')</script>");
                 //Response.Redirect("~/Users/usracc.aspx");
                }
                cnn.Close();
        }

        protected void canbtn_Click(object sender, EventArgs e)
        {
            Response.Redirect("~/Users/usracc.aspx");
        }

    }
}